using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;

namespace Elte.SqlSpIdx.Indices
{
    /// <summary>
    /// This class represent a grid index structure
    /// </summary>
    public class GridIndex : Index
    {

        private static Dictionary<string, GridIndex> cache = new Dictionary<string, GridIndex>();

        /// <summary>
        /// Constructor with Sqlconnection
        /// </summary>
        /// <param name="cn"></param>
        public GridIndex(SqlConnection cn)
            : base(cn)
        {
            type = IndexType.Grid;
        }

        /// <summary>
        /// Constructor with SqlTransaction
        /// </summary>
        /// <param name="tn"></param>
        public GridIndex(SqlTransaction tn)
            : base(tn)
        {
            type = IndexType.Grid;
        }

        private int Size
        {
            get
            {
                return int.Parse(options["size"]);
            }
        }









        private static GridIndex GetIndex(SqlString name)
        {
            GridIndex index;

            if (!cache.ContainsKey(name.Value))
            {
                using (SqlConnection cn = new SqlConnection(Constants.MyConnectionString))
                {
                    cn.Open();

                    index = new GridIndex(cn);
                    index.Load(name.Value);

                    cn.Close();
                }

                cache.Add(name.Value, index);
            }
            else
            {
                index = cache[name.Value];
            }

            return index;
        }

        /// <summary>
        /// Build a grid index and stores it on the SPIDX table.
        /// </summary>
        /// <param name="name">The name of the Grid (index) to be created </param>
        /// <param name="description">Description of the index</param>
        /// <param name="table">The table name to build on (make sure that the given table exists before running this method)</param>
        /// <param name="primaryKeyColumn">One primary key column (make sure that the given column exists and is a primary key)<note>For performance reasons -especially for large table- this column should be integer and NOT NULL</note></param>
        /// <param name="indexColumn">The column for store the rows grid-id (actually is the id of the bouding Grid)<note>This column is added if not exists, otherwise it become overwritten</note></param>
        /// <param name="dataColumn">not used</param>
        /// <param name="dataColumnNames">the name of data columns (to be indexed) comma seperated. Example: <c>'R,G,Z'</c></param>
        /// <param name="options">specific option to use, treedept and size requiered (same). Example: <c>'size=10,treedepth=10'</c></param>
        public override void CreateIndex(string name, string description, string table, string primaryKeyColumn, string indexColumn, string dataColumn, string dataColumnNames, string options)
        {
            bool newtransaction = false;
            if (databaseTransaction == null)
            {
                databaseTransaction = databaseConnection.BeginTransaction();
                newtransaction = true;
            }
            try
            {

                GridIndex index = new GridIndex(databaseTransaction);

                index.Name = name;
                index.Description = description;
                index.Table = table;
                index.IndexColumn = indexColumn;
                index.PrimaryKeyColumn = primaryKeyColumn;
                index.DataColumn = dataColumn;
                index.DataColumnNames = dataColumnNames;
                index.Options = options;
                index.GetMinMax();


                //if (index.Whitening)
                //    index.CalculateWhiteningMatrix();



                Core.Log("Creating Grid...\r\n");
                index.CreateSchema();

                Core.Log("Saving Grid...\r\n");
                index.Save();
            }
            catch (Exception ex)
            {
                Core.Log("Error: " + ex.Message + "\r\n");
            }
            if (newtransaction) databaseTransaction.Commit();



        }

        /// <summary>
        /// Set the index id for the data table
        /// </summary>
        public void UpdateTable()
        {

            string sql;
            SqlCommand cmd;
            this.DeleteTableIndex(Table, "IX_" + Table + "_Grid");

            sql = "UPDATE " + this.Table
                   + " SET " + this.IndexColumn + " = ";
            string postfix = "";
            for (int k = 0; k < this.Dimensions; k++)
            {
                sql += (k == 0 ? "" : "+");
                if ((max[k] - min[k]) < 0.0001)
                {
                    sql += "0";
                }
                else
                {
                    sql += "floor((" + this.DataColumnNameForSelect(k) + " - ("
                        + this.min[k].ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + ")) *  "
                        + this.Size.ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + " / "
                        + (this.max[k] - this.min[k]).ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + " ) ";
                    sql += postfix;
                }
                postfix += "*" + this.Size.ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat);

            }


            cmd = new SqlCommand(sql, databaseTransaction.Connection, databaseTransaction);
            cmd.CommandTimeout = 0;
            cmd.ExecuteNonQuery();

            if (!this.BinaryDataColumn)
            {
                //Create the index on that column
                sql = "CREATE NONCLUSTERED INDEX [IX_" + Table + "_Grid] ON [dbo].[" + Table + "]([";
                sql += IndexColumn + "] ASC ) INCLUDE ( [" + PrimaryKeyColumn + "],[";
                for (int k = 0; k < this.Dimensions; k++)
                {
                    sql += dataColumnNamesArray[k] + (k == this.Dimensions - 1 ? "]" : "],[");
                }
                sql += ") WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]";

                cmd = new SqlCommand(sql, databaseTransaction.Connection, databaseTransaction);
                cmd.CommandTimeout = 0;
                cmd.ExecuteNonQuery();


            }
        }



        /// <summary>
        /// Method used to search for the nearest neighbors of a point using the grid index
        /// </summary>
        /// <param name="coord">Coordinates of the center (search) point</param>
        /// <param name="count">Specify the number of points to return</param>
        /// <returns>Returns the IDs and the distances</returns>
        public override IEnumerable NearestNeighborsIndex(double[] coord, int count)
        {
            double temp;
            //the grid cells (ids) which have been queried already from the sql server
            System.Collections.ArrayList Leaves = new System.Collections.ArrayList();

            //we can stop when we the last point is closer to v 
            //then the bounding area's sides 
            bool needsearch = true;

            //Indicates whether we need to have wider bounding are,
            //or the current is not fully completed
            //when the area can not be queryied in one trip
            bool needescalation = false;

            //True only at first time,
            //we need to calculate the bounding distance, but no need to escalate
            bool firstrun = true;


            //The actual nearest N distances, the 0th is temp variable
            double[] distances = new double[count + 1];
            for (int k = 0; k <= count; k++)
            {
                distances[k] = double.MaxValue;
            }

            //Identity of the rows
            long[] ids = new long[count + 1];

            //The begining of the sql select is as the follows:
            StringBuilder SelectStringStart = new StringBuilder("select  top " + count.ToString() + " ");


            string distanceselect = "";

            for (int k = 0; k < coord.Length; k++)
            {
                distanceselect += ("(" + this.DataColumnNameForSelect(k) + "-(" + coord[k].ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + "))*("
                + this.DataColumnNameForSelect(k) + "-(" + coord[k].ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + "))"
                    + (k < coord.Length - 1 ? "+" : ""));
            }

            SelectStringStart.Append(this.PrimaryKeyColumn + ", " + distanceselect + " from " + this.Table + " where " + this.IndexColumn + " in (");


            //Actual cell: used later
            int[] actualcell = new int[coord.Length];

            //boundingmin/max, coordinates for the actual queried bounding box
            //at start it is a n dimensional cube all side 1 
            int[] boundingmin = new int[coord.Length];
            int[] boundingmax = new int[coord.Length];
            for (int k = 0; k < coord.Length; k++)
            {
                boundingmin[k] = (int)Math.Floor((coord[k] - this.min[k]) * this.Size / (this.max[k] - this.min[k]));
                if (boundingmin[k] < 0) boundingmin[k] = 0;
                if (boundingmin[k] > this.Size - 1) boundingmin[k] = this.Size - 1;
                boundingmax[k] = (int)boundingmin[k] + 1;
            }

            //store distances from the sides in both directions
            double[] distancemin = new double[coord.Length];
            double[] distancemax = new double[coord.Length];

            //stores the  distance from the point to the nearest side
            double boundingdistance = double.MaxValue;
            int nearestside = 0;
            while (needsearch)
            {
                //the static begining of the sql select 
                StringBuilder Selectstring = new StringBuilder(SelectStringStart.ToString());
                if (needescalation)
                {
                    if (nearestside < 0)
                    {
                        boundingmin[-nearestside - 1]--;
                    }
                    else
                    {
                        boundingmax[nearestside - 1]++;
                    }
                }
                if (firstrun || needescalation)
                {
                    //Determine to distances from the sides
                    //and decide which direction should it grows
                    boundingdistance = double.MaxValue;
                    nearestside = coord.Length + 1;
                    for (int k = 0; k < coord.Length; k++)
                    {
                        //reached the "edge" of space
                        if (boundingmin[k] <= 0)
                            //no need to go further in this direction
                            distancemin[k] = double.MaxValue;
                        else
                            //distance of the point from the side 
                            distancemin[k] = (double)(coord[k] - this.min[k] - boundingmin[k] * (this.max[k] - this.min[k]) / this.Size);


                        //reached the "edge" of space
                        if (boundingmax[k] >= this.Size)
                            //no need to go further in this direction  
                            distancemax[k] = double.MaxValue;
                        else
                            //distance of the point from the side 
                            distancemax[k] = (double)(boundingmax[k] * (this.max[k] - this.min[k]) / this.Size + this.min[k] - coord[k]);

                        if (boundingdistance > distancemin[k])
                        {
                            //till now, this is the nearest side
                            // -k means the kth dimension and - for the direction
                            nearestside = -k - 1;
                            boundingdistance = distancemin[k];
                        }
                        if (boundingdistance > distancemax[k])
                        {
                            //till now, this is the nearest side
                            // +k means the kth dimension and + for the direction
                            nearestside = k + 1;
                            boundingdistance = distancemax[k];
                        }
                    }

                    //Check whether the whole area affected
                    if (nearestside == coord.Length + 1) needsearch = false;
                    needescalation = false;
                    firstrun = false;
                }

                //Calc how many cells are there in the bounding box
                int num = 1;
                for (int k = 0; k < coord.Length; k++)
                {
                    num *= (boundingmax[k] - boundingmin[k]);
                }


                //For each cell
                int cellnumtemp;
                for (int i = 0; i < num; i++)
                {
                    //i th cell
                    cellnumtemp = i;
                    for (int k = 0; k < coord.Length; k++)
                    {
                        // num *= (boundingmax[k] - boundingmin[k]);
                        //wow
                        cellnumtemp = Math.DivRem(cellnumtemp, (boundingmax[k] - boundingmin[k]), out actualcell[k]);
                        actualcell[k] += boundingmin[k];
                    }

                    //calculate the cell id
                    cellnumtemp = 0;
                    for (int k = coord.Length - 1; k >= 0; k--)
                    {
                        cellnumtemp *= this.Size;
                        cellnumtemp += actualcell[k];
                    }

                    //Check whether it is already queried
                    if (!Leaves.Contains(cellnumtemp))
                    {
                        //add it to the list, and to the sql select string
                        Leaves.Add(cellnumtemp);
                        Selectstring.Append(cellnumtemp.ToString() + ",");
                        //Core.Log(cellnumtemp.ToString());
                    }
                }
                //remove the last ","
                Selectstring.Remove(Selectstring.Length - 1, 1);
                //add ")"
                Selectstring.Append(")");

                //Order by the distance
                //It is usefull when N<<num of query points
                //ex: order by (pc0-12345)*(pc0-12345)*(pc1-6789)*(pc1-6789)*...(pc2-(" + pc2.ToString() + "))*(pc2-(" + pc2.ToString() + "))"
                Selectstring.Append(" order by " + distanceselect);


                //ActualDistance is used below:
                //the distance of the actual point from v
                double actualdistance;

                //n is used below:
                //the index for the new item insert
                int n;

                needescalation = true;

                SqlCommand command = new SqlCommand(Selectstring.ToString(), this.DatabaseConnection);

                SqlDataReader reader = command.ExecuteReader();

                using (reader)
                {
                    while (reader.Read() && needsearch)
                    {
                        //   Ncount++;
                        actualdistance = reader.GetDouble(1);

                        // id[0] = reader.GetInt64(v.Length);
                        actualdistance = (double)Math.Sqrt((double)actualdistance);
                        if (distances[count] > actualdistance)
                        {
                            //The index where we going to insert the new item
                            //at least it is the last element
                            n = count;
                            //While the prev distance is greater than we go up by one
                            //for huge (N) count values, this should be optimized using ordered list
                            while (n > 1 && distances[n - 1] > actualdistance) n--;

                            //Check whether items exists in the list
                            if (distances[n] != double.MaxValue)
                            {
                                //if yes, we move the whole list down by one
                                //for huge (N) count values, this should be optimized using ordered list
                                for (int i = count; i > n; i--)
                                {
                                    ids[i] = ids[i - 1];

                                    distances[i] = distances[i - 1];
                                }

                            }
                            //Insert the new item into the list
                            ids[n] = reader.GetInt64(0);
                            distances[n] = actualdistance;

                            //No need to continue search 
                            needsearch = (distances[count] > boundingdistance);

                        }
                    }

                }
            }



            //if (Microsoft.SqlServer.Server.SqlContext.IsAvailable)
            //{
            //    //return record
            //    SqlDataRecord record = new SqlDataRecord(
            //                new SqlMetaData(this.PrimaryKeyColumn, SqlDbType.BigInt)
            //        // ,new SqlMetaData("Dist", SqlDbType.Float)

            //    );

            //    //Send the results
            //    SqlContext.Pipe.SendResultsStart(record);
            //    for (int i = 1; i <= count; i++)
            //    {
            //        ////return all the coordinates
            //        //for (int k = 0; k < v.Length; k++)
            //        //{
            //        //    record.Setdouble(k, P[i][k]);
            //        //}
            //        record.SetInt64(0, ids[i]);
            //        //record.SetDouble(1, best[i]);
            //        SqlContext.Pipe.SendResultsRow(record);
            //        //Core.Log(best[i].ToString() + " " + i.ToString());
            //        //Core.Log(P[i][0].ToString() + " " + P[i][1].ToString());
            //    }
            //    SqlContext.Pipe.SendResultsEnd();
            //}
            System.Collections.Generic.List<long> result = new List<long>();
            for (int i = 1; i <= count; i++)
            {
                result.Add(ids[i]);
            }
            return result;
            //        Core.Log(sqltime.ElapsedMilliseconds.ToString() + " ms SQL server , "
            //    + totaltime.ElapsedMilliseconds.ToString() + " ms total , " + roundtrip.ToString() + " roundtrips");

        }

        /// <summary>
        ///     no new tables requiered   
        ///     but have to add the new column to the table
        /// </summary>
        protected override void CreateSchema()
        {
            string sql;
            //add the new column for the target table
            sql = Elte.SqlSpIdx.Indices.IndexScripts.Addcolumn;
            sql = sql.Replace("$Datatable", Table);
            sql = sql.Replace("$indexColumn", IndexColumn);
            using (SqlCommand cmd = new SqlCommand(sql, DatabaseConnection, DatabaseTransaction))
            {
                cmd.ExecuteNonQuery();
            }
            UpdateTable();
        }

        /// <summary>
        ///     Nothing to delete
        ///     Only the new column should be removed
        /// </summary>
        protected override void DeleteSchema()
        {
            SqlCommand cmd = new SqlCommand("Alter table " + Table + " drop column " + IndexColumn, databaseTransaction.Connection, databaseTransaction);
            cmd.CommandType = CommandType.Text;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
            }
        }



        public override int LookupIndex(double[] coord)
        {


            int res = 0;
            int tempres = 0;
            int pow = 1;
            for (int k = 0; k < coord.Length; k++)
            {
                tempres = (int)Math.Floor((coord[k] - this.min[k]) * this.Size / (this.max[k] - this.min[k]));
                if (tempres < 0) { tempres = 0; };
                if (tempres > this.Size - 1) tempres = this.Size - 1;
                res += tempres * pow;
                pow *= this.Size;
            }

            return res;
        }



        public override IEnumerable SpherePoints(double[] coord, double R)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        private List<double[]> BoundingBoxes;
        private void GenerateBoundingBoxes()
        {
            BoundingBoxes = new List<double[]>();
            for (int i = 0; i < Size; i++)
            {

            }
        }

        private IEnumerable<int> Cellids(string spatialwhere)
        {
            return CellidsSphere(spatialwhere);
        }
        private IEnumerable<int> CellidsSphereRecursive(double[] center, double radiussqr, int cellid)
        {
            //nearest distance
            double distancemin = 0;
            //farest distance
            double distancemax = 0;
            double distanceup, distancedown;
            for (int i = 0; i < this.Dimensions; i++)
            {
                distanceup = BoundingBoxes[cellid][i * 2] - center[i];
                distancedown = BoundingBoxes[cellid][i * 2 + 1] - center[i];
                if (distanceup < 0)
                {
                    distanceup = -distanceup;
                    distancedown = -distancedown;

                }
                if (distancedown <= 0)
                {
                    if (distanceup > -distancedown)
                    {
                        distancemax += distanceup * distanceup;
                    }
                    else
                    {
                        distancemax += distancedown * distancedown;
                    }
                }
                else
                    if (distancedown < distanceup)
                    {
                        distancemin += distancedown * distancedown;
                        distancemax += distanceup * distanceup;
                    }
                    else
                    {
                        distancemin += distanceup * distanceup;
                        distancemax += distancedown * distancedown;
                    }

            }
            if (distancemin <= radiussqr)
            {

                if (cellid >= Math.Pow(2, this.Size)) yield return cellid;
                else
                {
                    foreach (int i in CellidsSphereRecursive(center, radiussqr, cellid * 2)) yield return i;
                    foreach (int i in CellidsSphereRecursive(center, radiussqr, cellid * 2 + 1)) yield return i;
                }

            }
        }
        private IEnumerable<int> CellidsSphere(string spatialwhere)
        {
            //distance(cx=1,cy=2,cz=3)<5;

            double[] center = new double[this.Dimensions];
            double radius = 0;
            spatialwhere = spatialwhere.ToLower();
            if (spatialwhere.StartsWith(@"distance("))
            {
                spatialwhere = spatialwhere.Remove(0, 9);
                for (int i = 0; i < this.Dimensions; i++)
                {
                    spatialwhere = spatialwhere.Remove(0, spatialwhere.IndexOf("=") + 1);
                    center[i] = double.Parse(spatialwhere.Substring(0, spatialwhere.Contains(",") ? spatialwhere.IndexOf(",") : spatialwhere.IndexOf(@")")), System.Globalization.CultureInfo.InvariantCulture.NumberFormat);
                    spatialwhere = spatialwhere.Remove(0, (spatialwhere.Contains(",") ? spatialwhere.IndexOf(",") : spatialwhere.IndexOf(@")")) + 1);
                }
                spatialwhere = spatialwhere.Trim();
                spatialwhere = spatialwhere.Remove(0, 1);
                radius = double.Parse(spatialwhere, System.Globalization.CultureInfo.InvariantCulture.NumberFormat);
            }

            foreach (int i in CellidsSphereRecursive(center, radius * radius, 1)) yield return i;
        }

        public override IEnumerable<object[]> SpatialQuery(string selectlist, string spatialwhere, string sqlwhere)
        {
            if (this.DatabaseConnection.State != ConnectionState.Open)
            {
                this.DatabaseConnection.Open();
            }
            //which cells involved?
            IEnumerator<int> involvedcells = Cellids(spatialwhere).GetEnumerator();

            //convert spatial where condition to sql too
            sqlwhere = sqlwhere == "" ? Core.Spatial2sql(spatialwhere, Dimensions) : "(" + sqlwhere + ")" + " and " + Core.Spatial2sql(spatialwhere, Dimensions);

            object[] results = null;
            string cellstring = "";
            while (involvedcells.MoveNext())
            {
                cellstring += "," + involvedcells.Current.ToString();
            }
            if (cellstring.Length > 0)
            {

                cellstring = cellstring.Remove(0, 1);
            }
            else
            {
                cellstring = "-1";
            }

            //while (involvedcells.MoveNext())
            //{
            // int actualcell = involvedcells.Current;
            string sql = Elte.SqlSpIdx.Indices.BalancedTree.BalancedTreeScripts.KD_QueryBox;
            sql = sql.Replace("$columns", selectlist); //((count == 0) ? "" : (" top " + count.ToString())) + " " + columns
            sql = sql.Replace("$Table", this.Table);
            sql = sql.Replace("$Indexname", this.IndexColumn);
            sql = sql.Replace("=$cellnumber", " in (" + cellstring + ")");
            //sql = sql.Replace("$cellnumber", actualcell.ToString());
            sql = sql.Replace("[$where]", sqlwhere == "" ? "" : "and " + sqlwhere);
            //if (SqlContext.IsAvailable) SqlContext.Pipe.Send(sql.ToString());
            bool firsread = true;

            using (SqlCommand cmd = new SqlCommand(sql, this.databaseConnection))
            {
                using (SqlDataReader r = cmd.ExecuteReader())
                {
                    while (r.Read())
                    {
                        if (firsread)
                        {
                            firsread = false;
                            results = new object[r.VisibleFieldCount];
                        }
                        r.GetValues(results);
                        yield return results;
                    }
                }

            }
            //}


        }

       

    }
}
